import pandas as pd
import sqlite3
from datetime import datetime

def main():
    # 读取退款Excel文件
    df = pd.read_excel(r'C:\Users\Administrator\Desktop\退款查询.xlsx', header=0)
    
    # 处理日期格式
    df['日期'] = pd.to_datetime(df['日期']).apply(lambda x: x.strftime('%Y%m%d'))
    
    # 订单金额转为负值
    df['订单金额'] = -df['订单金额']
    
    # 设置订单来源为2.1
    df['来源'] = 2.1
    
    # 添加业务类型列（默认为空）
    df['业务类型'] = ''
    
    # 添加备注列（用于存储关联的订单编号）
    df['备注'] = ''
    
    # 连接SQLite数据库
    conn = sqlite3.connect(r'C:\Users\Administrator\OneDrive\Database\STORE_SOLD.db')
    cursor = conn.cursor()
    
    # 遍历退款数据，更新原订单记录并插入退款记录
    for index, row in df.iterrows():
        # 查找原订单记录（来源为2的订单）
        cursor.execute("SELECT * FROM store_sold WHERE 订单ID = ? AND 来源 = 2", (row['订单ID'],))
        original_order = cursor.fetchone()
        
        if original_order:
            # 获取原订单的列名
            cursor.execute("PRAGMA table_info(store_sold)")
            columns = [column[1] for column in cursor.fetchall()]
            original_order_dict = dict(zip(columns, original_order))
            
            # 更新原订单记录，添加退款订单编号到备注
            cursor.execute("UPDATE store_sold SET 备注 = ? WHERE 订单ID = ? AND 来源 = 2", 
                          (f"退款订单号: {row['订单编号']}", row['订单ID']))
            
            # 在退款记录中添加原订单编号到备注
            df.at[index, '备注'] = f"原订单号: {original_order_dict['订单编号']}"
            
            # 退款订单的业务类型继承原订单
            df.at[index, '业务类型'] = original_order_dict['业务类型']
    
    # 使用pandas的to_sql方法将退款数据导入数据库
    df.to_sql('store_sold', conn, if_exists='append', index=False)
    
    conn.commit()
    conn.close()
    print('退款数据已成功导入数据库。')

if __name__ == '__main__':
    main()